package com.eleadmin.damon.utils.results;

import com.eleadmin.common.core.web.JsonResult;
import com.eleadmin.damon.utils.utils.DateUtil;
import net.sf.json.JSONObject;
import org.apache.poi.ss.formula.functions.T;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SingleColumnRowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author : Doman
 * @date : 2019-08-30 16:27:48
	 * @version ：1.0
	 */

	public class BaseDao
	{
		@Autowired
		public NamedParameterJdbcTemplate jdbcTemplate;
		Logger log = LoggerFactory.getLogger(this.getClass());
		/**
		 * 构造查询总数的SQL
		 *
     * @param strSQL 需要处理的SQL语句
     * @return 返回经过处理的查询总数的SQL
     */
		public String getCountSQL(String strSQL, Map<String, String> map) {

			// 格式化SQL语句
			String strSQL2 = strSQL.replaceAll("\\s+", " ").trim();
			String regx = "select count\\s*\\(\\s*(\\*|\\w+|\\w+\\.\\*|\\w+\\.\\w+)\\s*\\)\\s+from\\s+";
			Pattern pt = Pattern.compile(regx, Pattern.CASE_INSENSITIVE);
			Matcher mc = pt.matcher(strSQL2);
			// 判断是否已经是查询总数的SQL
			if (mc.find() && mc.start() == 0)
			{
				getMapParameter(new StringBuffer(strSQL), map);
				return strSQL;
			}
			String reg2 = " order by ";
			Pattern ptOrderNo = Pattern.compile(reg2, Pattern.CASE_INSENSITIVE);
			Matcher mOrderNo = ptOrderNo.matcher(strSQL2);
			int indexOrderNo = -1;//, idx = -1, endCondition = -1;
			while (mOrderNo.find())
			{
				// 查找最后一个order by 的位置
				indexOrderNo = mOrderNo.start();
			}

			if (indexOrderNo>0)
			{
				// 删除order by后面的SQL部分
				strSQL2 = strSQL2.substring(0, indexOrderNo);
				strSQL = strSQL2;
			}
			StringBuffer sqlbuf = new StringBuffer(strSQL.length() + 30);
			sqlbuf.append("select count(1) from (");
			sqlbuf.append(strSQL);
			sqlbuf.append(") aaaa");
			strSQL = sqlbuf.toString();
			getMapParameter(sqlbuf, map);
			return strSQL;
		}


		public String getCountSQL2(String strSQL, Map<String, String> map) {
			// 格式化SQL语句
			StringBuffer sqlbuf = new StringBuffer(strSQL.length() + 30);
			sqlbuf.append("select count(1) from (");
			sqlbuf.append(strSQL);
			sqlbuf.append(") aaaa");
			strSQL = sqlbuf.toString();
			getMapParameter(sqlbuf, map);
			return strSQL;
		}

	// 封装分页
	public String getPageSql(String sql, PageResult2<T> page, Map<String, String> map) throws Exception
	{
		if(page.getPage() ==0 || page.getLimit() == 0){throw new Exception("没有获得参数.");}
		String pagedSql = sql + " limit " + (page.getPage()-1)*page.getLimit() + ", " + page.getLimit();
		getMapParameter(new StringBuffer(pagedSql), map);
		return pagedSql;
	}

	public PageResult2<T> getPageQueryListNotOrderBy(PageResult2<T> page, String sql, Map<String, String> map, RowMapper rowMapper) throws Exception
	{
		String sqlCount = "select count(1) from ("+sql+") ccddd";
		getMapParameter(new StringBuffer(sqlCount), map);
		//获取数据总数
		long count = jdbcTemplate.queryForObject(sqlCount,map,new SingleColumnRowMapper<Integer>(Integer.class));

		//获取查询列表
		List returnList = jdbcTemplate.query(this.getPageSql(sql,page,map),map,rowMapper);
		DataResult<T> data = new DataResult<T>();
		data.setCount(count);
		data.setList(returnList);
		page.setData(data);
		return page;
	}

	public PageResult2<T> getPageQueryList(PageResult2<T> page, String sql, Map<String, String> map, RowMapper rowMapper) throws Exception
	{
		//获取数据总数
		long count = jdbcTemplate.queryForObject(this.getCountSQL2(sql,map),map,new SingleColumnRowMapper<Integer>(Integer.class));
		//获取查询列表
		List returnList = jdbcTemplate.query(this.getPageSql(sql,page,map),map,rowMapper);
		DataResult<T> data = new DataResult<T>();
		data.setCount(count);
		data.setList(returnList);
		page.setData(data);
		return page;
	}

	public void getMapParameter(StringBuffer sql, Map<String, String> args){
		StringBuffer s = new StringBuffer();
		String sql2 = sql.toString();
		//1
//		String str =sql.toString();
		if(null!=args){
			for (Map.Entry<String, String> entry : args.entrySet()) {
				//2
//				str = str.toString().replaceAll(":"+entry.getKey(),"'"+entry.getValue()+"'");
				if(s.length()>0){s.append(",");}
				s.append(entry.getKey() + ":" + entry.getValue());
				sql2 = sql2.replace(":"+entry.getKey(), entry.getValue()==null?"''":"'"+entry.getValue()+"'");
			}
		}
		//3
//		log.info("时间:{}\n SQL:{}\n 参数:{}", DateUtil.getCurrentDate(), str, s);
		log.info("\n时间:{}\nSQL:{}\n参数:{}\nAll Sql: {}\n", DateUtil.getCurrentDate(), sql.toString(), s,sql2);
//		System.out.println(DateUtil.getCurrentDate());
//		System.out.println(sql.toString());
//		System.out.println(s);
	}

	public void getArrayParameter(StringBuffer sql, Object[] arg) {
		StringBuffer s = new StringBuffer();
		for (int i = 0; i < arg.length; i++) {
			s.append(arg[i]+"; ");
		}
		log.info("时间:{}\n SQL:{}\n 参数:{}", DateUtil.getCurrentDate(), sql.toString(), s);
//		System.out.println(DateUtil.formatDate(new Date()));
//		System.out.println(sql.toString());
//		System.out.println(s.toString());
	}
	/**
	 * <p>
	 * 转换为 layui-table 需要的分页格式 JSON
	 * </p>
	 *
	 * @param page 分页对象
	 * @return
	 */
	public String jsonPage(PageResult2<T> page) {
		JSONObject data = new JSONObject();
		data.put("data", page.getData());
		data.put("msg", "查询成功");
		data.put("code", 0);
//		System.out.println(data.toString());
		return data.toString();
	}
	/**
	 * <p>
	 * 转换为 layui-table 需要的分页格式 JSON
	 * </p>
	 *
	 * @param page 分页对象
	 * @return
	 */
	public JSONObject jsonPage2(PageResult2<T> page) {
		JSONObject data = new JSONObject();
		data.put("data", page.getData());
		data.put("message", "查询成功");
		data.put("code", 0);
		return data;
	}

	public JsonResult jsonResult(PageResult2<T> page) {
		JsonResult data = new JsonResult();
		data.put("data", page.getData());
		data.put("message", "查询成功");
		data.put("code", 0);
		return data;
	}
}